package gruppe7.model.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import gruppe7.model.DataProvider;
import gruppe7.model.ProviderException;
import gruppe7.model.Station;
import gruppe7.model.User;
import gruppe7.model.UserException;

public class MySQLDataProvider implements DataProvider {

	public MySQLDataProvider() {
		
	}
	
	@Override
	public User getUserByName(String username) throws ProviderException, UserException {
		
		String sql = "SELECT userid FROM users WHERE username=?";
		Connection conn = null;
		
		try {
			conn = DBAccess.getInstance().getConnection();
			
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, username);
			
			ResultSet rs = stmt.executeQuery();
			if (!rs.next()) throw new UserException("User " + username + " not found");
			
			int userID = rs.getInt("userID");
			rs.close();
			
			sql = "SELECT rolename FROM tomcat_users_roles WHERE username=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, username);
			
			rs = stmt.executeQuery();
			if (!rs.next()) throw new UserException("Userrole not found");
			
			String role = rs.getString("rolename");
			rs.close();
			
			boolean isAdmin = (role != null && role.length() > 0 && role.equals("admin")) ? true : false;
			conn.close();
			
			return new User(username, isAdmin, userID);
		}
		
		catch (Exception e) {
			throw new ProviderException(e);
		}
		
		finally {
			try {
				if (conn != null) conn.close();
			}
			
			catch (Exception e) { /* ignorer, undskyld */ }
		}
	}

	@Override
	public float getUserBalance(User user) throws ProviderException {
		
		Connection conn = null;
		String username = user.getUsername();
		String sql = "SELECT account FROM users WHERE userName =?";
		
		try {
			conn = DBAccess.getInstance().getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, username);
			
			ResultSet rs = stmt.executeQuery();
			if (!rs.next()) throw new UserException("Unable to get balance from " + username);
			
			float balance = rs.getFloat("account");
			conn.close();
			
			return balance;
		}
		
		catch (Exception e) {
			throw new ProviderException(e);
		}
		
		finally {
			try {
				if (conn != null) conn.close();
			}
			
			catch (Exception e) { /* ignorer, undskyld */ }
		}
	}

	@Override
	public boolean updateUserBalance(User user, float balance) throws ProviderException {
		
		Connection conn = null;
        String sql = "UPDATE users SET account = ? WHERE username = ?";
        
        try {
        	conn = DBAccess.getInstance().getConnection();
        	PreparedStatement stmt = conn.prepareStatement(sql);
        	
        	stmt.setFloat(1, balance);
			stmt.setString(2, user.getUsername());
			
			stmt.execute();
        	conn.close();
        	
        	return true;
        }
		
        catch (Exception e) {
        	throw new ProviderException(e);
        }
        
        finally {
			try {
				if (conn != null) conn.close();
			} catch (Exception e) { e.printStackTrace(); }
		}
	}

	@Override
	public String getManagerByStationId(int stationID) throws ProviderException {
		
		String sql = "SELECT name FROM manager WHERE stationID = ?";
		Connection conn = null;
		
		try {
			conn = DBAccess.getInstance().getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setInt(1, stationID);
			
			ResultSet rs = stmt.executeQuery();
			
			String name = "unmanned";
			if (rs.next()) {
				name = rs.getString("name");
			}
			
			rs.close();
			conn.close();
			
			return name;
		}
		
		catch (Exception e) {
			throw new ProviderException(e);
		}
		
		finally {
			try {
				if (conn != null) conn.close();
			} catch (Exception e) { e.printStackTrace(); }
		}
	}

	@Override
	public Station getStationById(int stationID) throws ProviderException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Station getStationByName(String name) throws ProviderException {
		
		Connection conn = null;
		String sql = "SELECT * from station where name = ?";
		
		try {
			conn = DBAccess.getInstance().getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			
			ResultSet rs = stmt.executeQuery();
			if (!rs.next()) throw new ProviderException("Station not found");
			
			int zone = rs.getInt("zone");
			int id = rs.getInt("stationID");
			String erectionDate = rs.getString("erectiondate");
			String comment = rs.getString("comment");
			
			Station station = new Station(name, zone, id);
			station.setErectionDate(erectionDate);
			station.setZone(zone);
			
			station.setManager(getManagerByStationId(id));
			
			rs.close();
			conn.close();
			
			return station;
		}
		
		catch (Exception e) {
			throw new ProviderException(e);
		}
		
		finally {
			try {
				if (conn != null) conn.close();
			} catch (Exception e) { e.printStackTrace(); }
		}
		
	}

	@Override
	public List<String> getStationList() throws ProviderException {
		
		ArrayList<String> stationList = new ArrayList<String>();
		String sql = "SELECT name FROM station";
		Connection conn = null;
		
		try {
			conn = DBAccess.getInstance().getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				String name = rs.getString("name");
				stationList.add(name);
			}
			
			rs.close();
			conn.close();
			
			return stationList;
		}
		
		catch (Exception e) {
			throw new ProviderException(e);
		}
		
		finally {
			try {
				if (conn != null) conn.close();
			} catch (Exception e) { e.printStackTrace(); }
		}
	}

	@Override
	public void updateStationInfo(Station station) throws ProviderException {
		// TODO Auto-generated method stub
		
	}

}
